package com.group1.onlineeducation.sugar.mapper;

import com.group1.onlineeducation.sugar.bean.*;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TradeMapper {

    @Select("select  sc,\n" +
            "        sum(uv_ct) AS uv,\n " +
            "       sum(sv_ct) AS sv,\n " +
            "       sum(pv_ct) / sum(sv_ct) AS pv_sv_avg,\n " +
            "       sum(dur_sum) / sum(sv_ct) /1000/60 AS dur_sv_avg,\n " +
            "       sum(uj_ct) / sum(sv_ct) * 100 AS uj_rate " +
            "from dws_traffic_page_view_window " +
            "where toYYYYMMDD(stt)= #{date} " +
            "group by sc")
    List<ScTraffic> scTraffic(int date);

    @Select("select toHour(stt) AS hour,\n" +
            "       sum(uv_ct) AS uv,\n" +
            "       sum(pv_ct) AS pv,\n" +
            "       sum(pv_ct)/sum(sv_ct) AS pvSvAvg " +
            "from dws_traffic_page_view_window " +
            "where toYYYYMMDD(stt)= #{date} " +
            "group by stt")
    List<HourTraffic> hourTraffic(int date);

    @Select("select is_new,\n" +
            "       sum(uv_ct) AS uv,\n" +
            "       sum(pv_ct)  AS pv,\n" +
            "       sum(uj_ct)/sum(sv_ct) AS uj_sv_avg,\n" +
            "       sum(dur_sum)/sum(uv_ct) AS dur_uv_avg,\n" +
            "       sum(pv_ct)/sum(uv_ct) AS pv_uc_avg " +
            "from dws_traffic_page_view_window " +
            "where toYYYYMMDD(stt)= #{date} " +
            "group by is_new")
    List<IsNewTraffic> isNewTraffic(int date);

    @Select("select word keyword,\n" +
            "       sum(word_ct)*1 score " +
            "from dws_traffic_keyword_page_view_window " +
            "where toYYYYMMDD(stt)= #{date} " +
            "group by word")
    List<KwTraffic> kwTraffic(int date);

    @Select("select province_name,\n" +
            "       sum(order_amount) AS order_amount,\n" +
            "       sum(order_num_ct) AS order_num_ct,\n" +
            "       sum(order_time_ct) AS order_time_ct\n" +
            "from dws_trade_order_province_window\n" +
            "where toYYYYMMDD(stt)= #{date}\n" +
            "group by province_name")
    List<ProvinceTrade> provinceTrade(int date);
}
